package com.spring.schedule_system.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.spring.schedule_system.entity.Classroom;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository("ClassroomDao")
public interface ClassroomDao extends BaseMapper<Classroom> {
    @Update("update tb_classroom set classroom_status=0 where ")
    int udpateClassroom();
    /*
    sql:
    select  DISTINCT classroom_id ,classroom_address,
    classroom_name ,classroom_status,
    class_weeks,weeks,number_sections
	from tb_classroom ,tb_schedule1
	where tb_schedule1.scheduled_classroom=SUBSTRING(classroom_address,4,6)
	and class_weeks like '%2%'
	and weeks='二' and
	number_sections like '%1、2%');

	mybatis:
	select  DISTINCT classroom_id ,classroom_address,
    classroom_name ,classroom_status,
    class_weeks,weeks,number_sections
	from tb_classroom ,tb_schedule1
	where tb_schedule1.scheduled_classroom=SUBSTRING(classroom_address,4,6)
	and class_weeks like '%2%'
	and weeks='二' and
	number_sections like '%1、2%');
     */
    @Select("select  DISTINCT classroom_id ,classroom_address,classroom_count,classroom_name \n" +
            "\t        from tb_classroom ,tb_schedule1 \n" +
            "\t        where tb_schedule1.scheduled_classroom=SUBSTRING(classroom_address,4,6)\n" +
            "\t        and class_weeks like CONCAT('%',#{class_weeks},'%')\n" +
            "\t        and weeks=#{weeks} and\n" +
            "\t        number_sections like CONCAT('%',#{number_sections},'%')")
    List<Classroom> selectClassRoomList(String class_weeks, String weeks, String number_sections);
    /*
    sql:
    select classroom_id ,
    classroom_address,
    classroom_name,
    classroom_count
	from tb_classroom
	where tb_classroom.classroom_id not in(select  DISTINCT classroom_id
	from tb_classroom ,tb_schedule1
	where tb_schedule1.scheduled_classroom=SUBSTRING(classroom_address,4,6)
	and class_weeks like '%2%'
	and weeks='二' and
	number_sections like '%1、2%');

    mybatis:
    select classroom_id ,classroom_address,classroom_name,classroom_count
	from tb_classroom
	where tb_classroom.classroom_id not in(select  DISTINCT classroom_id
	from tb_classroom ,tb_schedule1
	where tb_schedule1.scheduled_classroom=SUBSTRING(classroom_address,4,6)
	and class_weeks like CONCAT('%',#{class_weeks},'%')
	and weeks='二' and
	number_sections like CONCAT('%',#{number_sections},'%')
     */
    @Select("select classroom_id ,classroom_address,classroom_name,classroom_count\n" +
            "\tfrom tb_classroom\n" +
            "\twhere tb_classroom.classroom_id not in(select  DISTINCT classroom_id\n" +
            "\tfrom tb_classroom ,tb_schedule1\n" +
            "\twhere tb_schedule1.scheduled_classroom=SUBSTRING(classroom_address,4,6)\n" +
            "\t        and class_weeks like CONCAT('%',#{class_weeks},'%')\n" +
            "\t        and weeks = #{weeks} and\n" +
            "\t        number_sections like CONCAT('%',#{number_sections},'%'))")
    List<Classroom> selectRoomListKongXian(String class_weeks,String weeks, String number_sections);
}
